﻿------创建人：王涛       创建时间：2010年8月6日 16：17-------
------2010年8月9日 11：05    添加联合查询-------
------2010年8月10日 14：00    修改预案状态查询条件逻辑判断条件-------
USE ASERDB
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='Proc_GetEaer_Plans' AND TYPE='P')
BEGIN
  DROP PROCEDURE Proc_GetEaer_Plans
END
GO
CREATE Procedure [dbo].[Proc_GetEaer_Plans]
 	@PageIndex int,
	@PageSize int,
	@PlanName varchar(50),
	@PlanType varchar(10),
	@PlanStatus varchar(10),
	@TempPlanStatus varchar(10),
	@Field varchar(10),
	@Scope varchar(10),
	@PublishDept varchar(50),
	@LDate varchar(20),
	@NDate varchar(20),
	@Promulgator varchar(50)
 AS
DECLARE @SQL varchar(5000)
DECLARE @RecordCount int,@P1 INT
DECLARE @Where VARCHAR(1000)
IF @PageIndex >1 SET @PageIndex = (@PageIndex-1)*@PageSize +1
SET @Where = ''
--查询条件--
--预案名称--
IF ((@PlanName IS NOT NULL) AND (RTRIM(LTRIM(@PlanName))<>''))
BEGIN
  SET @Where = @Where +  ' AND PlanName LIKE ''%'+ @PlanName + '%'''
END
--预案类型--
IF ((@PlanType IS NOT NULL) AND (RTRIM(LTRIM(@PlanType)) <>''))
BEGIN
	SET @Where = @Where +  ' AND PlanType = '''+ @PlanType + ''''
END
--预案状态--
IF (((@PlanStatus IS NOT NULL) AND (RTRIM(LTRIM(@PlanStatus)) <>'')) AND ((@TempPlanStatus IS NULL) OR (RTRIM(LTRIM(@TempPlanStatus)) ='')))
BEGIN
	SET @Where = @Where +  ' AND PlanStatus = '''+ @PlanStatus + ''''
END
--备用预案状态--
IF (((@TempPlanStatus IS NOT NULL) AND (RTRIM(LTRIM(@TempPlanStatus)) <>'')) AND ((@PlanStatus IS NULL) OR (RTRIM(LTRIM(@PlanStatus)) ='')))
BEGIN
	SET @Where = @Where +  ' AND PlanStatus = '''+ @TempPlanStatus + ''''
END
--两种预案状态--
IF (((@TempPlanStatus IS NOT NULL) AND (RTRIM(LTRIM(@TempPlanStatus)) <>'')) AND ((@PlanStatus IS NOT NULL) AND (RTRIM(LTRIM(@PlanStatus)) <>'')))
BEGIN
	SET @Where = @Where +  ' AND (PlanStatus = '''+ @PlanStatus + ''' OR PlanStatus = '''+ @TempPlanStatus + ''')'
END
--预案适用领域--
IF ((@Field IS NOT NULL) AND (RTRIM(LTRIM(@Field)) <>''))
BEGIN
	SET @Where = @Where +  ' AND Field = '''+ @Field + ''''
END
--预案适用范围--
IF ((@Scope IS NOT NULL) AND (RTRIM(LTRIM(@Scope)) <>''))
BEGIN
	SET @Where = @Where +  ' AND Scope = '''+ @Scope + ''''
END
--发布部门/单位--
IF ((@PublishDept IS NOT NULL) AND (RTRIM(LTRIM(@PublishDept))<>''))
BEGIN
  SET @Where = @Where +  ' AND PublishDept LIKE ''%'+ @PublishDept + '%'''
END
--发布起始时间--
IF ((@LDate IS NOT NULL) AND (RTRIM(LTRIM(@LDate)) <>''))
BEGIN
	SET @Where = @Where +  ' AND PublishDate > '''+ @LDate + ''''
END
--发布结束时间--
IF ((@NDate IS NOT NULL) AND (RTRIM(LTRIM(@NDate)) <>''))
BEGIN
	SET @Where = @Where +  ' AND PublishDate < '''+ @NDate + ''''
END
--签发人--
IF ((@Promulgator IS NOT NULL) AND (RTRIM(LTRIM(@Promulgator))<>''))
BEGIN
  SET @Where = @Where +  ' AND Promulgator LIKE ''%'+ @Promulgator + '%'''
END

IF ((LEN(@Where)>0) AND (LEFT(@Where,5)=' AND')) 
BEGIN
	SET @Where = Right(@Where,LEN(@Where)-5)
END
SET @SQL = N'SELECT * FROM  Eaer_Plan ORDER BY PlanID DESC'
IF (LEN(@Where)>0)
BEGIN
  SET @SQL = 'SELECT *,
c.Name as StatusName,
o.Name as TypeName,
m.Name as FieldName,
d.Name as ScopeName
FROM  Eaer_Plan e 
left join CommonCode c on c.Code=e.PlanStatus 
left join CommonCode o on o.Code=e.PlanType
left join CommonCode m on m.Code=e.Field
left join CommonCode d on d.Code=e.Scope WHERE ' + @Where + ' ORDER BY PlanID DESC'
END
EXEC sp_cursoropen @P1 OUTPUT, @SQL, @scrollopt = 1, @ccopt = 335873, @rowcount = @RecordCount OUTPUT 
IF (@P1 != 0)
BEGIN
  EXEC sp_cursorfetch @P1, 32, @PageIndex, @PageSize 
  EXEC sp_cursorclose @P1
END
select @RecordCount
PRINT @SQL 
return @@ERROR